Read date format from Excel

Hi all!

I have a problem to read cell contents from excel when it is in date format. I got the error message:
"Method 'Value' returned type '7', which is not handled by DOORS (only string, char, bool, int and IDispatch supported)"
when trying to read the cell.

My function to read the cell uses the "Value" property. If "Value" is changed to "Value2" the error message disappears but nothing is returned.

The code:

string funcGetCellValue(OleAutoObj xlSheet, string sCellRef, string &sValue)
{
    OleAutoObj xlCell
    string sErrmsg
    
    sErrmsg = funcGetCell(xlSheet, sCellRef, xlCell)
    if ( !null(sErrmsg) )
    {
        return sErrmsg
    }
    sErrmsg = oleGet(xlCell, "Value", sValue)
    return sErrmsg
}

Is there any way around this problem (beside changing the contents of the cells)?

/Anders

a_vestlin - Wed Feb 16 04:40:11 EST 2011

Re: Read date format from Excel
a_vestlin - Wed Feb 16 05:21:15 EST 2011

Hi again!

I found a solution. It is possible to use the "Text" property instead, which will return the displayed text.

/Anders

Re: Read date format from Excel
SystemAdmin - Wed Feb 16 06:33:28 EST 2011

a_vestlin - Wed Feb 16 05:21:15 EST 2011
Hi again!

I found a solution. It is possible to use the "Text" property instead, which will return the displayed text.

/Anders

But then you may be sensitive to Excel Locale. Maybe you should use CDbl to convert the date to a Double, and then make some computation to change this to a Long counting seconds from 1st Jan 1970 --- I am not sure but I think that visual counts days from 1st Jan 1900 rather than seconds from 1st Jan 1970 --- and finally convert this back to a date in DXL with dateOf.

BR,
Vincent.

Re: Read date format from Excel
SystemAdmin - Wed Feb 16 06:40:41 EST 2011

In VBA I get:

Debug.Print CDate(1.5)

     31/12/1899 12:00:00

So VB counts dates from 30 December 1899 at 00:00:00

So you have to add 25569 days and then multiply by 86400 seconds,

Dim dYourVBDate As Date, lYourDXLDate As Long

     lYourDXLDate = Round((CDbl(dYourVBDate)-25569)*86400)